# -*- coding: utf-8 -*-

from wt.connector.db_manager.db_untils_tools import DBUntilsTools
from wt.connector.data_connect import DataConnect


class DBopenGaussManager(DataConnect, DBUntilsTools):

    def __init__(self, ):
        super().__init__()

    def define_col(self):
        """
        定义psql 插入字段类型
        :return:
        """
        id = self.get_incre_time()
        col = {
            "id": "serial,",
            "c_02_integer_col": "integer,",
            "c_03_smallint_col": "smallint,",
            "c_04_bigint_col": "bigint,",
            "c_05_double_col": "numeric(9,2),",
            "c_06_float_col": "numeric(9,2),",
            "c_07_decimal_col": "numeric(7,2),",
            "c_08_date_col": "date,",
            "c_09_time_col": "time,",
            "c_10_timestamp_col": "timestamp,",
            "c_11_char_col": "varchar(50)",
            "": ")"
        }
        return col

    def define_ddl(self, db, table):
        """
        拼接oracle ddl语句
        :param table:
        :return:
        """
        col = self.define_col()
        ddl_sql = 'CREATE TABLE {} ( \r\n'.format(table, table)
        for key in col:
            line = key + " " + col[key] + "\r\n"
            ddl_sql = ddl_sql + line
        return ddl_sql

    def general_col_value(self, db, table):
        """
        生成 oracle dml
        :param table:
        :return:
        """
        c_02_integer_val = self.get_random(-2147483648, 2147483647)
        c_03_smallint_val = self.get_random(-32768, 32767)
        c_04_bigint_val = self.get_random(-9223372036854775808, 9223372036854775807)
        c_05_double_val = self.get_random_float(-500, 500)
        c_06_float_val = self.get_random_float(-500, 500)
        c_07_decimal_val = self.get_random_float(-32768, 32767)
        c_08_date_val = "to_date({},'yyyy-mm-dd')".format(self.get_random_date())
        c_09_time_val = "{}".format(self.get_random_time())
        c_10_timestamp_val = "to_timestamp({}, 'YYYY-MM-DD HH24:MI:SS')".format(self.get_random_datetime())
        c_11_char_val = self.get_random_string(30)

        sql = "insert into {} (c_02_integer_col, c_03_smallint_col, c_04_bigint_col, c_05_double_col, c_06_float_col," \
              "c_07_decimal_col, c_08_date_col, c_09_time_col, c_10_timestamp_col, c_11_char_col) " \
              "values ({},{},{},{},{},{},{},{},{},{})" \
              "".format(table, c_02_integer_val, c_03_smallint_val, c_04_bigint_val,
                        c_05_double_val, c_06_float_val, c_07_decimal_val, c_08_date_val, c_09_time_val,
                        c_10_timestamp_val, c_11_char_val
                        )
        return sql

    def define_dml(self, db, table, lines):
        """
        批量生成 oracle dml 语句
        :param table:
        :param lines:
        :return:
        """
        insert_sql = ""
        while lines > 0:
            tmp = self.general_col_value(db, table)
            insert_sql = insert_sql + tmp + "\r\n"
            lines = lines - 1
        print(insert_sql)
        return insert_sql

    def create_and_insert_sql(self, db, table, num, is_recreate=None):
        """
        生成ddl、插入dml
        :param is_recreate:
        :param db:
        :param table:
        :param num:
        :return:
        """
        if is_recreate:
            ddl_sql = self.define_ddl(table)
            self.exec_openGauss_db(db, "drop table {}".format(table))
            self.exec_openGauss_db(db, ddl_sql)
        insert_sql = self.define_dml(db, table, num)
        for line in insert_sql.strip().split("\r\n"):
            self.exec_openGauss_db(db, line)

    def set_combkey(self, db, table):
        line = "SELECT column_name FROM information_schema.columns WHERE table_name = {} ORDER BY ordinal_position LIMIT 1".format(
            table)
        print(line)
        line = "show server_version"
        columns = self.exec_openGauss_db(db, line)
        print(columns)
        column1 = columns[0][0]
        column2 = columns[1][0]
        print(column1, column2)

    def set_slot(self, db, slot):
        line = "SELECT pg_drop_replication_slot('{}')".format(slot)
        self.exec_openGauss_db(db, line)

    def get_count(self, db, table):
        sql = "select count(1) as cot from {}.{}".format(db, table)
        res = self.exec_openGauss_db(db, sql)
        print(res)
        if res['message'] == "Success":
            res_num = res['data'][0][0]
            return res_num
        else:
            return None

    def execute_drop_table(self, db, table):
        sql = "drop table {}.{}".format(db, table)
        res = self.exec_openGauss_db(db, sql)
        return self.set_return_massage(res)

    def cdc_set_on(self, db, table):
        sql = "ALTER TABLE {}  REPLICA IDENTITY FULL;".format(table)
        return self.exec_openGauss_db(db, sql)


if __name__ == '__main__':
    pass
